package com.sky.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {
    private File excel;//成员属性-指定要读写的Excel文件
    private Workbook workbook;//从依赖中使用的类，代表工作簿

    /**
     * 工具的构造方法，使用一个实体的文件实例化Excel
     *
     * @param excel
     */
    public ExcelUtil(File excel) {
        if (excel.exists()) {
            if (excel.getName().toLowerCase().endsWith(".xls")) {
                //如果文件名以.xls结尾，视为文件是Excel低版本的文件
                //计算机系统中的文件不区分大小写..Xls ,xls...
                this.excel = excel;
                try {
                    this.workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excel)));
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else if (excel.getName().toLowerCase().endsWith(".xlsx")) {
                try {
                    this.workbook = new XSSFWorkbook(new FileInputStream(excel));
                } catch (IOException e) {
                    e.printStackTrace();
                }
                this.excel = excel;
            } else {
                //说明不是Excel文件，不用读取数据
                this.workbook = null;
            }
        }
    }

    /**
     * 获取Excel文件中的某一个工作表中的某一个单元格的内容
     *
     * @param rowNum    -- 工资表的行数，第一行从0开始
     * @param columnNum --工资的列，第一列从0开始
     * @param sheetNum  --Excel文件中的第几个工作表，第一个工作表 从0开始
     *                  return 返回单元格中的内容（字符串的形式）
     */
    public String readDate(int rowNum, int columnNum, int sheetNum) {
        String content = "";
        if (this.workbook != null) {
            //Sheet sheet=this.workbook.getSheet("Sheet1");//根据工作表名称，从工作簿中返回实例化对象
            Sheet sheet = this.workbook.getSheetAt(sheetNum);//根据工作表顺序，从工作簿中返回实例化对象

            if (sheet != null) {
                Row row = sheet.getRow(rowNum);//得到工作表的第几行，0代表第一行，1为第二行...

                Cell cell = row.getCell(columnNum);//得到单元格，从当前行中，提取某一列，单元号(行，列)

                if (cell != null) {
                    //单元格被实例化之后，可以获取单元格中的内容
                    //单元格中的数据包含多种类型的数据，提取内容的时候，先判断
                    if (cell.getCellTypeEnum() == CellType.FORMULA) {//公式
                        content = cell.getNumericCellValue() + "";
                    } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                        content = cell.getBooleanCellValue() + "";
                    } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {//判断当前单元格是日期类型
                            SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            content = fmt.format(cell.getDateCellValue());
                        } else {
                            content = cell.getNumericCellValue() + "";
                        }
                    } else if (cell.getCellTypeEnum() == CellType.STRING) {
                        content = cell.getStringCellValue();
                    } else {
                        content = "";
                    }
                }
            }
        }
        return content;
    }

    public boolean write(int rowNum, int cloumnNum, int sheetNum, String content) {
        boolean result = false;
        Sheet sheet = workbook.getSheetAt(sheetNum);
        if (sheet == null) {
            sheet = workbook.createSheet();
        }
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }

        Cell cell = row.getCell(cloumnNum);

        if (cell == null) {
            cell = row.createCell(cloumnNum);
        }

        cell.setCellType(CellType.STRING);
        cell.setCellValue(content);
        try {
            FileOutputStream outputStream = new FileOutputStream(excel);
            workbook.write(outputStream);
            outputStream.close();
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
            result=false;
        }
        return result;
    }

    public int getRows(int sheetNum) {
        Sheet sheet = this.workbook.getSheetAt(sheetNum);
        return sheet.getPhysicalNumberOfRows();
    }

    public int getColumns(int sheetNum, int rowNum) {
        Sheet sheet = this.workbook.getSheetAt(sheetNum);
        if (sheet != null) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                return 0;
            } else
                return row.getPhysicalNumberOfCells();
        } else {
            return 0;
        }
    }

    public List<String> getColumnContent(int sheetNum, int columnNum) {
        List<String> list = new ArrayList<>();
        int i = this.getRows(sheetNum);
        for (int j = 0; j < i; j++) {
            list.add(readDate(j, columnNum, sheetNum));
        }
        return list;
    }

    public List<String> getRowContent(int sheetNum, int rowNum) {
        List<String> list = new ArrayList<>();
        int i = this.getColumns(sheetNum, rowNum);
        for (int j = 0; j < i; j++) {
            list.add(readDate(rowNum, j, sheetNum));
        }
        return list;
    }
}

